// repo.js
import crypto from "crypto";
import { pool } from "./db.js";

// 计算幂等哈希（同一批同一条数据避免重复）
function md5(s) {
  return crypto.createHash("md5").update(s).digest("hex");
}

/**
 * 批量写入（建议每10分钟传入 ≤100条）
 * @param {Array<Object>} rows  你的JSON数组（含中文key）
 * @param {Date} snapshotAt     这一批的写入时刻（建议统一用同一个时间）
 */
export async function upsertSnapshots(rows, snapshotAt = new Date()) {
  const conn = await pool.getConnection();
  try {
    await conn.beginTransaction();

    // 1) 确保“当天分区”存在
    await conn.query("CALL ensure_partition_for_ts(?)", [snapshotAt]);

    // 2) 预编译语句
    const sql = `
      INSERT INTO ticket_snapshots
      (query_group, travel_date, train_no, from_station, to_station,
       depart_time, arrive_time, seats_json, snapshot_at, record_hash)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ON DUPLICATE KEY UPDATE
        seats_json = VALUES(seats_json),
        snapshot_at = VALUES(snapshot_at)
    `;
    const stmt = await conn.prepare(sql);

    // 3) 批量执行
    for (const r of rows) {
      const group = r["查询组"];
      const tDate = r["日期"]; // 'YYYY-MM-DD'
      const train = r["车次"];
      const from = r["出发地"];
      const to = r["到达地"];
      const dTime = r["出发时间"]; // 'HH:mm'
      const aTime = r["到达时间"]; // 'HH:mm'
      const seats = JSON.stringify(r["余票席位"] || {});

      const hash = md5(
        `${group}|${tDate}|${train}|${from}|${to}|${dTime}|${aTime}|${seats}`
      );

      await stmt.execute([
        group,
        tDate,
        train,
        from,
        to,
        dTime,
        aTime,
        seats,
        snapshotAt,
        hash,
      ]);
    }

    await conn.commit();
  } catch (e) {
    await conn.rollback();
    throw e;
  } finally {
    if (conn) conn.release();
  }
}

/** 取某条线路在某出行日的“最新一次”快照 */
export async function getLatestSnapshot(trainNo, from, to, travelDate) {
  const sql = `
    SELECT *
    FROM ticket_snapshots
    WHERE train_no = ? AND from_station = ? AND to_station = ? AND travel_date = ?
    ORDER BY snapshot_at DESC
    LIMIT 1
  `;
  const rows = await pool.query(sql, [trainNo, from, to, travelDate]);
  return rows[0] || null;
}

/** 取某时间窗（或指定桶）的历史快照 */
export async function getHistory(trainNo, from, to, travelDate, since, until) {
  const sql = `
    SELECT id, seats_json, snapshot_at, snapshot_bucket
    FROM ticket_snapshots
    WHERE train_no = ? AND from_station = ? AND to_station = ? AND travel_date = ?
      AND snapshot_at BETWEEN ? AND ?
    ORDER BY snapshot_at
  `;
  return pool.query(sql, [trainNo, from, to, travelDate, since, until]);
}
